**************************
** CREATE REFUGEES DATA **
**************************

/* dataset refugees available after contacting Swedish Migration Agency */
import excel "refugeesMonthly.xlsx", firstrow clear

* remove unknown municipality
drop if municipality=="KOMMUN/LAN SAKNAS"

* transform municipality and county in lower cases (except first letter)
replace municipality=substr(municipality, 1, 1)+ lower(substr(municipality, 2, .))
replace county=substr(county, 1, 1)+ lower(substr(county, 2, .))

save "refugeesMonthly.dta", replace


** merge with dataset refugees by accommodation type, dataset available after contacting Swedish Migration Agency
import excel "refugeesAccomodation.xlsx", firstrow clear

* reshape to have one obs per municipality
encode Accommodation, gen(housing)
drop Accommodation
reshape wide refugees_201412 refugees_201501 refugees_201502 refugees_201503 refugees_201504 refugees_201505 refugees_201506 refugees_201507 refugees_201508 refugees_201509 refugees_201510 refugees_201511 refugees_201512 refugees_201601 refugees_201602 refugees_201603 , i(municipality) j(housing)

* rename refugees variable with ABO, EBO

*ABO
rename refugees_2014121 refugeesABO_201412

foreach j in 01 02 03 04 05 06 07 08 09 10 11 12{
rename refugees_2015`j'1 refugeesABO_2015`j'
}

foreach j in 01 02 03 {
rename refugees_2016`j'1 refugeesABO_2016`j'
}

*EBO
rename refugees_2014122 refugeesEBO_201412

foreach j in 01 02 03 04 05 06 07 08 09 10 11 12{
rename refugees_2015`j'2 refugeesEBO_2015`j'
}

foreach j in 01 02 03 {
rename refugees_2016`j'2 refugeesEBO_2016`j'
}

* remove unknown municipality
drop if municipality=="KOMMUN/LAN SAKNAS"

* transform municipality and county in lower cases (except first letter)
replace municipality=substr(municipality, 1, 1)+ lower(substr(municipality, 2, .))

save "refugeesAccommodation.dta", replace

merge 1:1 municipality using "refugeesMonthly.dta"
drop _merge

save "numrefugees.dta", replace


** merge with accommodation dataset **

* dataset accommodation available after contacting Per Loman
import excel "ABOhousing.xlsx", firstrow clear

* transform municipality and county in lower cases (except first letter)
replace municipality=substr(municipality, 1, 1)+ lower(substr(municipality, 2, .))

* merge with number refugees
merge 1:1 municipality using "numrefugees.dta"
drop _merge

* replace housing to 0 if no beds available
foreach j in 01 02 03 04 05 06 07 08 09 10 11 12{
replace housing_2015`j'=0 if housing_2015`j'==.
}

foreach j in 01 02 {
replace housing_2016`j'=0 if housing_2016`j'==.
}

** create new variables

* average number of refugees
foreach j in 01 02 03 04 05 06 07 08 09 10 11 12{
egen mrefugees_2015`j'=mean(refugees_2015`j')
label variable mrefugees_2015`j' "average number of refugees in month 2015`j' "
}

foreach j in 01 02 03 {
egen mrefugees_2016`j'=mean(refugees_2016`j')
label variable mrefugees_2016`j' "average number of refugees in month 2016`j'"
}

* dummy if above mean refugees
gen manyrefugees=1 if (refugees_201501>mrefugees_201501) & (refugees_201502>mrefugees_201502) & (refugees_201503>mrefugees_201503) & (refugees_201504>mrefugees_201504) & (refugees_201505>mrefugees_201505) & (refugees_201506>mrefugees_201506) & (refugees_201507>mrefugees_201507) & (refugees_201508>mrefugees_201508) & (refugees_201509>mrefugees_201509) & (refugees_201510>mrefugees_201510)& (refugees_201511>mrefugees_201511) & (refugees_201512>mrefugees_201512)& (refugees_201601>mrefugees_201601)& (refugees_201602>mrefugees_201602)
replace manyrefugees=0 if manyrefugees==.

* percentage change of number refugees
bysort municipality: gen perc_change_refugees= (refugees_201602 - refugees_201502)/refugees_201502
label variable perc_change "percentage change in num refugees from feb 2015 to feb 2016"


save "final_refugees.dta", replace


